import pymysql
class DBHelper:
    #打开数据库
    def openDB(self):
        #把局部变量变成全局变量
        global conn
        conn=pymysql.connect(host="127.0.0.1",
                             user="root",
                             password="root123456",
                            database="test",
                            port=3306,charset="utf8")
        global baoma
        baoma=conn.cursor()

    #关闭数据库
    def closeDB(self):
        baoma.close()
        conn.close()
        
    def edit(self,sql):
        self.openDB()
        result=0
        try:
            result=baoma.execute(sql)
            conn.commit()
        except Exception as e:
            result=-1
            conn.rollback()
            print(e)
        finally:
            self.closeDB()  
        return result
        
    def chaOne(self,sql):
        self.openDB()
        baoma.execute(sql)
        result=baoma.fetchone()
        self.closeDB()
        return result
    
    def chaAll(self,sql):
        self.openDB()
        baoma.execute(sql)
        result=baoma.fetchall()
        self.closeDB()
        return result
     
class MovieTypeDAL(DBHelper):
    # 1.录⼊电影类型信息
    def insert(self,mt):
        sql=f"insert into MovieType values({mt.tid},'{mt.tname}','{mt.tcontent}',now())"
        return self.edit(sql)
    
    # 2.1 显示tid的数据内容
    def showBytid(self,tid):
        sql=f"select * from MovieType where tid='{tid}'"
        return self.chaOne(sql)
    
    # 2.2修改电影类型信息
    def update(self,mt):
        # sql=f"update MovieType set tname='{mt.tname}',tcontent='{mt.tcontent}' where tid={mt.tid}"
        # res=self.edit(sql)
        # return res

        edtname=0
        edtcontent=0
        if(mt.tname!=""):
            sql1=f"update MovieType set tname='{mt.tname}' where tid={mt.tid}"
            edtname=self.edit(sql1)

        if(mt.tcontent!=""):
            sql2=f"update MovieType set tcontent='{mt.tcontent}' where tid={mt.tid}"
            edtcontent=self.edit(sql2)

        return edtname,edtcontent
        
    # 3.删除电影类型信息
    def delet(self,tid):
        sql=f"delete from MovieType where tid={tid}"
        return self.edit(sql)

    # 4.显示所有电影类型信息
    def showall(self):
        sql="select *,DATE_FORMAT(TDATE,'%Y年%m月%d日') from MovieType"
        return self.chaAll(sql)
    
class MovieDAL(DBHelper):
    # 录⼊电影信息
    def insert(self,m):
        sql=f"insert into Movie values({m.id},'{m.title}','{m.mdate}',{m.score},{m.tid})"
        return self.edit(sql)

    # 获取某类型的所有电影列表
    def showBytid(self,tid):
        sql=f"""
        select id,title,mdate,score,tname from MovieType mt 
        inner join Movie m on mt.tid=m.tid 
        where mt.tid={tid}
            """
        return self.chaAll(sql)
    
    # 获取某一编号的电影信息
    def showByid(self,id):
        sql=f"""
        select id,title,mdate,score,m.tid,tname from MovieType mt 
        inner join Movie m on mt.tid=m.tid 
        where id={id}
            """
        return self.chaOne(sql)
    
    # 修改电影信息
    def update(self,m):
        sql=f"update Movie set title='{m.title}',mdate='{m.mdate}',score={m.score},tid={m.tid} where id={m.id}"
        return self.edit(sql)
    
    # 删除
    def delete(self,id):
        sql=f"delete from Movie where id={id}"
        return self.edit(sql)
    
    # 模糊搜索
    def showBytitle(self,title):
        sql=f"""select id,title,mdate,score,tname from MovieType mt 
        inner join Movie m on mt.tid=m.tid 
        where title like '%{title}%'"""
        return self.chaAll(sql)
    
    # 各类型电影的数量
    def getMTCount(self):
        sql="""select tname,t.sl from 
        (select tid,count(1) as sl from Movie group by tid) as t
        inner join MovieType mt on mt.tid=t.tid"""
        return self.chaAll(sql)
    
    # 电影各类型的平均分
    def getMTScore(self):
        sql="""SELECT mt.tname,ROUND(AVG(m.score), 1) AS avgscore
            FROM Movie m JOIN MovieType mt ON m.tid = mt.tid
            GROUP BY mt.tname"""
        return self.chaAll(sql)